package com.codemao.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;

import com.codemao.bean.javaBean;
import com.google.gson.Gson;

public class Sqltojson {
    public static void main(String[] args) {
        try {
            // 调用Class.forName()方法加载驱动程序
            //Class.forName("com.mysql.jdbc.Driver");
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("成功加载MySQL驱动！");

            String url = "jdbc:mysql://localhost:3306/xyjson?serverTimeZone=Asia/Shanghai"; // JDBC的URL
            Connection conn;

            conn = DriverManager.getConnection(url, "root", "root");
            Statement stmt = conn.createStatement(); // 创建Statement对象
            System.out.println("成功连接到数据库！");

            String sql = "SELECT * FROM region_area"; // 要执行的SQL
            ResultSet rs = stmt.executeQuery(sql);// 创建数据对象

            saveListData(rs);//分类存数据
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /*
     * 存数据
     */
    public static void saveListData(ResultSet rs) {
        try {
            ArrayList<javaBean> province = new ArrayList();
            ArrayList<javaBean> city = new ArrayList();
            ArrayList<javaBean> country = new ArrayList();
            while (rs.next()) {
                int area_deep = rs.getInt(2);//此处是spl数据中的地区深度（1，2，3-->省市区）
                if (area_deep == 0) {
                    javaBean theatyBean = new javaBean();
                    theatyBean.level = rs.getString(2);
                    theatyBean.parent_code = rs.getString(3);
                    theatyBean.area_code = rs.getString(4);
                    theatyBean.name = rs.getString(7);
                    theatyBean.short_name = rs.getString(8);
                    province.add(theatyBean);

                } else if (area_deep == 1) {
                    javaBean theatyBean = new javaBean();
                    theatyBean.level = rs.getString(2);
                    theatyBean.parent_code = rs.getString(3);
                    theatyBean.area_code = rs.getString(4);
                    theatyBean.name = rs.getString(7);
                    theatyBean.short_name = rs.getString(8);
                    city.add(theatyBean);
                } else if (area_deep == 2) {
                    javaBean theatyBean = new javaBean();
                    theatyBean.level = rs.getString(2);
                    theatyBean.parent_code = rs.getString(3);
                    theatyBean.area_code = rs.getString(4);
                    theatyBean.name = rs.getString(7);
                    theatyBean.short_name = rs.getString(8);
                    country.add(theatyBean);
                }

            }
            RankBeanList(province, city, country);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 循环遍历
    private static void RankBeanList(ArrayList<javaBean> province,ArrayList<javaBean> city, ArrayList<javaBean> country) {

        LinkedList<LinkedHashMap<String, Object>> provinceList = new LinkedList<LinkedHashMap<String, Object>>();// 省集合
        for (int i = 0; i < province.size(); i++) {
            LinkedHashMap<String, Object> prMap = new LinkedHashMap<String, Object>();
            prMap.put("level", province.get(i).level);
            prMap.put("area_code", province.get(i).area_code);
            prMap.put("parent_code", province.get(i).parent_code);
            prMap.put("name", province.get(i).name);
            prMap.put("short_name", province.get(i).short_name);
            List<LinkedHashMap<String, Object>> citylist = new ArrayList<LinkedHashMap<String, Object>>();// 市集合

            for (int j = 0; j < city.size(); j++) {
                if (province.get(i).area_code.equals( city.get(j).parent_code)) {
                    LinkedHashMap<String, Object> cityMap = new LinkedHashMap<String, Object>();
                    cityMap.put("level", city.get(j).level);
                    cityMap.put("area_code", city.get(j).area_code);
                    cityMap.put("parent_code", city.get(j).parent_code);
                    cityMap.put("name", city.get(j).name);
                    cityMap.put("short_name", city.get(j).short_name);
                    List<LinkedHashMap<String, Object>> arealist = new ArrayList<LinkedHashMap<String, Object>>();// 区名字集合

                    for (int k = 0; k < country.size(); k++) {
                        if (city.get(j).area_code.equals(country.get(k).parent_code)) {
                            LinkedHashMap<String, Object> countryMap = new LinkedHashMap<String, Object>();
                            countryMap.put("level", country.get(k).level);
                            countryMap.put("area_code", country.get(k).area_code);
                            countryMap.put("parent_code", country.get(k).parent_code);
                            countryMap.put("name", country.get(k).name);
                            countryMap.put("short_name", country.get(k).short_name);
                            arealist.add(countryMap);
                        }
                    }
                    cityMap.put("children",arealist);// 市下面的区集合
                    citylist.add(cityMap);

                }
                prMap.put("children", citylist);// 省下面的市集合
            }
            provinceList.add(prMap);

        }
        mapPrintln(provinceList);// 输出
    }

    // 输出JSON文件
    private static void mapPrintln(List<LinkedHashMap<String, Object>> list) {
        if (list == null && list.size() == 0) {
            return;
        }
        Gson gson = new Gson();
        String jsonString = gson.toJson(list);
        CreateFileUtil.createJsonFile(jsonString,"D:\\Desktop","province");
    }
}